﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'P_GetChildrenTreeBasicReferenceValue')
	BEGIN
		DROP  Procedure  esf_sso.P_GetChildrenTreeBasicReferenceValue
	END
GO

-- esf_sso.P_GetChildrenTreeBasicReferenceValue '25119,25140', 23


CREATE Procedure esf_sso.P_GetChildrenTreeBasicReferenceValue
(
	@pParentBasicReferenceIDs VARCHAR (MAX)
	,@pBasicReferenceTypeID INT = NULL
)
AS

	DECLARE @query VARCHAR (MAX)


	SET @query = 
	' FROM
		esf_sso.TreeBasicReferenceValue T
	INNER JOIN 
		esf_sso.VBasicReference BR ON BR.ID = T.BasicReferenceID_' + CONVERT (VARCHAR (MAX), (dbo.FUBCountChar (@pParentBasicReferenceIDs, ',') + 1)) +
	' WHERE 1 = 1 '
	
	IF @pBasicReferenceTypeID IS NOT NULL
	BEGIN
		SET @query = @query + ' AND BR.BasicReferenceTypeID = ' + CONVERT (VARCHAR (MAX), @pBasicReferenceTypeID)
	END
		
	SET @query = @query + ' ' + dbo.FUBConcatenateSplitString (@pParentBasicReferenceIDs, ',', ' AND BasicReferenceID_', '', 0, 0, 1, 0, 1)
	
	IF @pBasicReferenceTypeID IS NOT NULL
	BEGIN
		SET @query = @query + ' UNION ALL SELECT BR.*, NULL FROM esf_sso.VBasicReference BR WHERE BR.BasicReferenceTypeID = ' + CONVERT (VARCHAR (MAX), @pBasicReferenceTypeID) + 
			' AND BR.ID NOT IN (SELECT BR.ID ' + @query + ')'
	END

	SET @query = 'SELECT BR.*, T.Value ' + @query

	EXEC  (@query)
